package excel.countryMedicine;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import po.MyObject;
import util.AbstractExl;

/**
 * @author suwy
 * @date 2013-8-2
 * @todo not to work !!!
 */
public class LogoutImport extends AbstractExl {
	
	private static final Logger LOG = Logger.getLogger("LogoutImport");
	private static String fileName = "注销或撤销批准文号进口药品";
	private static String filePath = "E:/downloadData/药监局/"+fileName+"/";
	private static String outputFolder = "E:/OutputToExcel/药监局/";
	private int rowNum = 3562;
	private static String regex = "<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
	+"<tr>.*?"
	+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">.*?</td>.*?"
	+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>";

	private void writeExl(List<MyObject> list) {
		File folder = new File(outputFolder);
		if (!folder.exists()) folder.mkdirs();
		FileOutputStream fOut = null;
		try {
			Workbook workbook = new SXSSFWorkbook(2000);
			Sheet sheet = workbook.createSheet("Sheet1");
			Row row = null;
			fOut = new FileOutputStream(outputFolder+fileName+".xlsx");
				for (int i = 0; i < list.size(); i++) {
					row = sheet.createRow(i);
					row.createCell((short)0).setCellValue(list.get(i).getObj1().trim());
					row.createCell((short)1).setCellValue(list.get(i).getObj2().trim());
					row.createCell((short)2).setCellValue(list.get(i).getObj3().trim());
					row.createCell((short)3).setCellValue(list.get(i).getObj4().trim());
					row.createCell((short)4).setCellValue(list.get(i).getObj5().trim());
					row.createCell((short)5).setCellValue(list.get(i).getObj6().trim());
					row.createCell((short)6).setCellValue(list.get(i).getObj7().trim());
					row.createCell((short)7).setCellValue(list.get(i).getObj8().trim());
					row.createCell((short)8).setCellValue(list.get(i).getObj9().trim());
					row.createCell((short)9).setCellValue(list.get(i).getObj10().trim());
					row.createCell((short)10).setCellValue(list.get(i).getObj11().trim());
					row.createCell((short)11).setCellValue(list.get(i).getObj12().trim());
					row.createCell((short)12).setCellValue(list.get(i).getObj13().trim());
					row.createCell((short)13).setCellValue(list.get(i).getObj14().trim());
					row.createCell((short)14).setCellValue(list.get(i).getObj15().trim());
					row.createCell((short)15).setCellValue(list.get(i).getObj16().trim());
					row.createCell((short)16).setCellValue(list.get(i).getObj17().trim());
					row.createCell((short)17).setCellValue(list.get(i).getObj18().trim());
					row.createCell((short)18).setCellValue(list.get(i).getObj19().trim());
					row.createCell((short)19).setCellValue(list.get(i).getObj20().trim());
					row.createCell((short)20).setCellValue(list.get(i).getObj21().trim());
					row.createCell((short)21).setCellValue(list.get(i).getObj22().trim());
					row.createCell((short)22).setCellValue(list.get(i).getObj23().trim());
					row.createCell((short)23).setCellValue(list.get(i).getObj24().trim());
					row.createCell((short)24).setCellValue(list.get(i).getObj25().trim());
					row.createCell((short)25).setCellValue(list.get(i).getObj26().trim());
					row.createCell((short)26).setCellValue(list.get(i).getObj27().trim());
					row.createCell((short)27).setCellValue(list.get(i).getObj28().trim());
					row.createCell((short)28).setCellValue(list.get(i).getObj29().trim());
					row.createCell((short)29).setCellValue(list.get(i).getObj30().trim());
					row.createCell((short)30).setCellValue(list.get(i).getObj31().trim());
					row.createCell((short)31).setCellValue(list.get(i).getObj32().trim());
					row.createCell((short)32).setCellValue(list.get(i).getObj33().trim());
					row.createCell((short)33).setCellValue(list.get(i).getObj34().trim());
					row.createCell((short)34).setCellValue(list.get(i).getObj35().trim());
//					LOG.info("It had "+i+" rows");
				}
			workbook.write(fOut);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != fOut) {
				try {
					fOut.close();
					LOG.info(fileName +" success export excel ! And the excel had " + list.size() + " rows.");
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}
	
	private void excute() {
		try {
			List<MyObject> list = new ArrayList<MyObject>();;
			StringBuffer content = null;
			MyObject o = new MyObject();
			o.setObj1("分包装企业地址");
			o.setObj2("分包装企业名称");
			o.setObj3("分包装文号有效期截止日");
			o.setObj4("注册证号");
			o.setObj5("公司名称（中文）");
			o.setObj6("公司名称（英文）");
			o.setObj7("地址（中文）");
			o.setObj8("地址（英文）");
			o.setObj9("国家（中文）");
			o.setObj10("国家（英文）");
			o.setObj11("产品名称（中文）");
			o.setObj12("产品名称（英文）");
			o.setObj13("剂型（中文）");
			o.setObj14("剂型（英文）");
			o.setObj15("规格（中文）");
			o.setObj16("规格（英文）");
			o.setObj17("包装规格（中文）");
			o.setObj18("包装规格（英文）");
			o.setObj19("生产厂商（中文）");
			o.setObj20("生产厂商（英文）");
			o.setObj21("厂商地址（中文）");
			o.setObj22("厂商地址（英文）");
			o.setObj23("厂商国家（中文）");
			o.setObj24("厂商国家（英文）");
			o.setObj25("进口检验标准（中文）");
			o.setObj26("进口检验标准（英文）");
			o.setObj27("发证日期");
			o.setObj28("有效期截止日");
			o.setObj29("产品类别");
			o.setObj30("商品名（中文）");
			o.setObj31("商品名（英文）");
			o.setObj32("注册证号备注");
			o.setObj33("原注册证号");
			o.setObj34("分包装批准文号");
			o.setObj35("分包装文号批准日期");
			list.add(o);
			for (int i = 1; i <= rowNum; i++) {
				content = readHtm(filePath+i+".htm");
				Pattern pattern = Pattern.compile(regex);
				Matcher matcher = pattern.matcher(content);
				while (matcher.find()) {
					o = new MyObject();
					o.setObj1(matcher.group(1).trim());
					o.setObj2(matcher.group(2).trim());
					o.setObj3(matcher.group(3).replace("至", "").trim());
					o.setObj4(matcher.group(4).trim());
					o.setObj5(matcher.group(5).trim());
					o.setObj6(matcher.group(6).trim());
					o.setObj7(matcher.group(7).trim());
					o.setObj8(matcher.group(8).trim());
					o.setObj9(matcher.group(9).trim());
					o.setObj10(matcher.group(10).trim());
					o.setObj11(matcher.group(11).trim());
					o.setObj12(matcher.group(12).trim());
					o.setObj13(matcher.group(13).trim());
					o.setObj14(matcher.group(14).trim());
					o.setObj15(matcher.group(15).trim());
					o.setObj16(matcher.group(16).trim());
					o.setObj17(matcher.group(17).trim());
					o.setObj18(matcher.group(18).trim());
					o.setObj19(matcher.group(19).trim());
					o.setObj20(matcher.group(20).trim());
					o.setObj21(matcher.group(21).trim());
					o.setObj22(matcher.group(22).trim());
					o.setObj23(matcher.group(23).trim());
					o.setObj24(matcher.group(24).trim());
					o.setObj25(matcher.group(25).trim());
					o.setObj26(matcher.group(26).trim());
					o.setObj27(matcher.group(27).trim());
					o.setObj28(matcher.group(28).replace("至", "").trim());
					o.setObj29(matcher.group(29).trim());
					o.setObj30(matcher.group(30).trim());
					o.setObj31(matcher.group(31).replace("至", "").trim());
					o.setObj32(matcher.group(32).trim());
					o.setObj33(matcher.group(33).trim());
					o.setObj34(matcher.group(34).trim());
					o.setObj35(matcher.group(35).trim());
				}list.add(o);
			}
				writeExl(list);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		LogoutImport instance = new LogoutImport();
		instance.excute();
	}
}